﻿Imports System.Data.OleDb
Public Class frmBangGia
    Public RowEditIndex As Integer

    Private Sub frmBangGia_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoaiSanPham_Load()
        SanPham_Load()
    End Sub
    Public Sub SanPham_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String ' = "Select * From SanPham Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai WHERE DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            If cbxLoaiSanPham.SelectedValue = 0 Then
                query = "Select * From (Select SanPham.IDLoai,SanPham.DonGia,SanPham.DVTSanPhamID,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,SanPham.GhiChu From SanPham " & _
                                   " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai" & _
                                   " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                   " Where SanPham.Xoa=0 And (MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%' Or TenSanPham Like N'%" & txtTenSanPhamSearch.Text & "%')) AS SP Where SP.DVTSanPhamID IS NOT NULL Order By IDLoai,TenSanPham"
            Else
                query = "Select * From (Select SanPham.IDLoai,SanPham.DonGia,SanPham.DVTSanPhamID,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,SanPham.GhiChu From SanPham " & _
                                   " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai" & _
                                   " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                   " Where SanPham.Xoa=0 And (MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%' Or TenSanPham Like N'%" & txtTenSanPhamSearch.Text & "%') ) AS SP Where SP.DVTSanPhamID IS NOT NULL And IDLoai=" & cbxLoaiSanPham.SelectedValue & " Order By IDLoai,TenSanPham"
            End If
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            dtSanPham.Columns.Add("sDonGia")
            Dim DonGia As Double
            Dim ci As New Globalization.CultureInfo("en-us")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                DonGia = Double.Parse(dtSanPham.Rows(i)("DonGia"), Globalization.NumberStyles.Currency)
                Dim s As String = DonGia.ToString("N02", ci)
                dtSanPham.Rows(i)("sDonGia") = s
            Next
            dgvSanPham.DataSource = dtSanPham
            If dgvSanPham.Rows.Count - 1 > 0 Then
                dgvSanPham.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub LoaiSanPham_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsLoaiSanPham As New DataSet
            Dim dtLoaiSanPham As New DataTable
            Dim query As String = "Select IDLoai,TenLoai From LoaiSanPham Union Select IdTatCa As IDLoai,TatCa As TenLoai From TatCa"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsLoaiSanPham)
            dbConn.Close()
            dtLoaiSanPham = dsLoaiSanPham.Tables(0)
            cbxLoaiSanPham.DataSource = dtLoaiSanPham
            cbxLoaiSanPham.ValueMember = "IdLoai"
            cbxLoaiSanPham.DisplayMember = "TenLoai"
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cbxLoaiSanPham_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxLoaiSanPham.SelectionChangeCommitted
        Try
            SanPham_Load()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham.CellClick
        Try
            RowEditIndex = e.RowIndex
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try

        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "Select * From SanPham Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai Where MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%' Or TenSanPham Like N'%" & txtTenSanPhamSearch.Text & "%'  Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            dtSanPham.Columns.Add("sDonGia")
            Dim DonGia As Double
            Dim ci As New Globalization.CultureInfo("en-us")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                DonGia = Double.Parse(dtSanPham.Rows(i)("DonGia"), Globalization.NumberStyles.Currency)
                Dim s As String = DonGia.ToString("N02", ci)
                dtSanPham.Rows(i)("sDonGia") = s
            Next
            dgvSanPham.DataSource = dtSanPham
            If dgvSanPham.Rows.Count - 1 > 0 Then
                dgvSanPham.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdPrice_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrice.Click
        Try
            frmThemGia.IDSanPham = Integer.Parse(dgvSanPham.Rows(RowEditIndex).Cells("IDSanPham").Value.ToString)
            ShowModalForm(frmThemGia, False, False)
            frmThemGia.IDSanPham = -1
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTextSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPhamSearch.TextChanged
        Try
            SanPham_Load()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cemConvertExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemConvertExcel.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsSanPham As New DataSet
                Dim dtSanPham As New DataTable
                Dim query As String
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                query = "Select MaLoai,MaSanPham,TenSanPham,TenDonViTinh,DonGia From (Select SanPham.IDLoai,SanPham.DonGia,SanPham.DVTSanPhamID,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,SanPham.GhiChu From SanPham " & _
                                   " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai" & _
                                   " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                   " Where SanPham.Xoa=0 And (MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%' Or TenSanPham Like N'%" & txtTenSanPhamSearch.Text & "%')) AS SP Where SP.DVTSanPhamID IS NOT NULL Order By IDLoai,TenSanPham"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsSanPham)
                dbConn.Close()
                'dgvSanPham.AutoGenerateColumns = False
                dtSanPham = dsSanPham.Tables(0)
                'dtSanPham.Columns.Add("STT")
                'dtSanPham.Columns.Add("sDonGia")
                Dim DonGia As Double
                Dim ci As New Globalization.CultureInfo("en-us")
                For i As Integer = 0 To dtSanPham.Rows.Count - 1
                    DonGia = Double.Parse(dtSanPham.Rows(i)("DonGia"), Globalization.NumberStyles.Currency)
                    Dim s As String = DonGia.ToString("N02", ci)
                    dtSanPham.Rows(i)("DonGia") = Format(Single.Parse(s), "N02")
                    'dtSanPham.Rows(i)("DonGia") = dtSanPham.Rows(i)("DonGia").to
                Next
                Dim ColumnName As New List(Of String)
                ColumnName.Add("Mã Loại")
                ColumnName.Add("Mã Sản Phẩm")
                ColumnName.Add("Tên Sản Phẩm")
                ColumnName.Add("Đơn Vị Tính")
                ColumnName.Add("Đơn Giá")
                ExportExcel.Class1.exportToExcel(dtSanPham.DataSet, Path & "/" & FileName & ".xls", ColumnName)
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
End Class